package ird.demo.tools;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.persistence.EmbeddedId;
import javax.persistence.Id;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class Db2Converter {
	public static SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
	public static String DB_NAME = "MIDB";
	public static String SCHEMA = "MIDEV";
	public static Map<String,String> DATA_TYPE_MAP = new HashMap<String,String>();
	public static Map<String,List<String>> DB_TABLE_MAP = new HashMap<String,List<String>>();
	public static String WORKING_FOLDER = "D:/01 work/program/Assess Contrl(Dummy)";
	static {
		DATA_TYPE_MAP.put("CHAR", "CHARACTER");
//		DATA_TYPE_MAP.put("VARCHAR", "VARCHAR");
//		DATA_TYPE_MAP.put("DATE", "DATE");
//		DATA_TYPE_MAP.put("SMALLINT", "SMALLINT");
		
		try{
//			FileInputStream is = new FileInputStream("D:/01 work/program/CT068/new CT068/CT068 Table.xlsx"); //文件流
//	        Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的
//	        Sheet sheet = workbook.getSheetAt(0);
//	        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
//                Row row = sheet.getRow(i);
//                if(row.getCell(1) == null){
//                	continue;
//                }
//                
//                String dbName = row.getCell(1).toString();
//                String tableName = row.getCell(3).toString();
//                List<String> tableList = DB_TABLE_MAP.get(dbName);
//                if(tableList == null){
//                	tableList = new ArrayList<String>();
//                }
//                if(tableName != null && !"".equals(tableName)){
//                	tableList.add(tableName);
//                }
//                DB_TABLE_MAP.put(dbName, tableList);
//            }
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	
	public static void xls2DDL(String fileName) throws Exception{
		
		StringBuilder sb = new StringBuilder();
		//sb.append("CONNECT TO MIDB USER \""+SCHEMA+"\";\r\n\r\n");
		File excelFile = new File(fileName); //创建文件对象
        FileInputStream is = new FileInputStream(excelFile); //文件流
        Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的
        int sheetCount = workbook.getNumberOfSheets();  //Sheet的数量
        
        List<String> entityNameList = new ArrayList<String>();
        
        //遍历每个Sheet
        for (int s = 0; s < sheetCount; s++) {
            Sheet sheet = workbook.getSheetAt(s);
            if(sheet.getPhysicalNumberOfRows() < 2){
            	continue;
            }
            System.out.println(sheet.getSheetName());
            String tableName = sheet.getSheetName();
            List<String> keyList = new ArrayList<String>();
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                if(row.getCell(0) == null || ("".equals(row.getCell(0).toString().trim()))){
                	continue;
                }
                
                if(i == 1){
                	sb.append("DROP TABLE MIDEV.HOWARD_" + tableName + ";\r\n\r\n");
                	sb.append("CREATE TABLE MIDEV.HOWARD_" + tableName + "(\r\n");
                }
                
                String fieldName = row.getCell(0).toString();
                String dataType = DATA_TYPE_MAP.get(row.getCell(1).toString());
                if(dataType == null){
                	dataType = row.getCell(1).toString();
                }
                String lengthStr = getLengthStr(dataType, row.getCell(2)+"");
                String nullable = getIsNullStr( row.getCell(4)+"");
                String isPk = row.getCell(3)+"";
                
                if("Y".equalsIgnoreCase(isPk)){
                	keyList.add(fieldName);
                }
                
                sb.append("	" + fieldName + " " + dataType + " " + lengthStr + nullable + ",\r\n");
            }
            sb.append(getKeyStr(tableName, keyList));
            sb.append(") ;\r\n\r\n\r\n\r\n");
            
            
            entityNameList.add(tableName);
        }
        //sb.append("CONNECT RESET;");
        
        File entityBeanDir = new File("D:/workspace4.3/MyJPA/src/demo/ird/domain");
        File outDir = new File(WORKING_FOLDER + "/generetedSrc");
        System.out.println(sb.toString().toUpperCase());
        generateServiceAndRepository(entityNameList, "ird.demo", entityBeanDir, outDir);
        
	}
	
	private static void generateServiceAndRepository(List<String> entityNameList, String pkgName, File entityBeanDir, File outDir) throws Exception{
		
		if(outDir.exists()){
			outDir.delete();
		}
		outDir.mkdirs();
		
		Map<String,String> bean2PkMap = new HashMap<String,String>();
		
		for(String entityName: entityNameList){
			String beanName = entityName.substring(0,1).toUpperCase() 
					+ entityName.substring(1).toLowerCase();
			
			
			
			String entityBeanContent = FileHelper.getFileContents(entityBeanDir.getAbsoluteFile() + "/" +entityName + ".java");
			String beanPK = getKeyTypeStr(entityBeanContent);
			
			bean2PkMap.put(beanName, beanPK);
			
			//generate Repository
			String reposotoryFileName = beanName + "Repository" + ".java";
			StringBuilder repositorySb = new StringBuilder();
			repositorySb.append("package "+pkgName+".repository;\r\n\r\n");
			repositorySb.append("import ird.demo.domain."+beanName+";\r\n");
			if(beanPK.endsWith("PK")){
				repositorySb.append("import ird.demo.domain."+beanName+"PK;\r\n");
			}
			repositorySb.append("\r\n");
			repositorySb.append("public interface "+beanName+"Repository extends BaseRepository< "+beanName+", "+beanPK+">{\r\n");
			repositorySb.append("}");
			FileHelper.writeStringToFile(new File(outDir.getAbsolutePath()+"/repository/"+reposotoryFileName), repositorySb.toString());
			
		}
		
		
		//generate Service&serviceImpl
		StringBuilder serviceSb = new StringBuilder();
		StringBuilder serviceImplSb = new StringBuilder();
		
		serviceSb.append("package "+pkgName+".service;\r\n\r\n");
		serviceImplSb.append("package "+pkgName+".service.impl;\r\n\r\n");
		
		for(String beanName : bean2PkMap.keySet() ){
			serviceSb.append("import "+pkgName+".domain."+beanName+";\r\n");
			serviceImplSb.append("import "+pkgName+".domain."+beanName+";\r\n");
			serviceImplSb.append("import "+pkgName+".repository."+beanName+"Repository;\r\n");
			
			if(bean2PkMap.get(beanName).endsWith("PK")){
				serviceSb.append("import ird.demo.domain."+beanName+"PK;\r\n");
				serviceImplSb.append("import ird.demo.domain."+beanName+"PK;\r\n");
			}
		}
		serviceSb.append("\r\n");
		serviceImplSb.append("import ird.demo.service.CommonService;\r\n");
		serviceImplSb.append("import org.springframework.beans.factory.annotation.Autowired;\r\n");
		serviceImplSb.append("import org.springframework.stereotype.Service;\r\n");
		serviceImplSb.append("\r\n");
		serviceImplSb.append("@Service\r\n");
		
		serviceSb.append("public interface CommonService{\r\n");
		serviceImplSb.append("public class CommonServiceImpl implements CommonService{\r\n");
		
		for(String beanName : bean2PkMap.keySet() ){
			serviceSb.append("	public "+beanName+" find"+beanName+"("+bean2PkMap.get(beanName)+" pk);\r\n");
			
			serviceImplSb.append("	@Autowired\r\n");
			serviceImplSb.append("	private "+beanName+"Repository "+beanName.toLowerCase()+"Repository;\r\n\r\n");
		}
		
		for(String beanName : bean2PkMap.keySet() ){
			
			serviceImplSb.append("	public "+beanName+" find"+beanName+"("+bean2PkMap.get(beanName)+" pk){\r\n");
			serviceImplSb.append("		return "+beanName.toLowerCase()+"Repository.findOne(pk);\r\n");
			serviceImplSb.append("	}\r\n\r\n");
		}
		
		
		serviceSb.append("}");
		serviceImplSb.append("}");
		
		String serviceFileName = "CommonService" + ".java";
		String serviceImplFileName = "CommonServiceImpl" + ".java";
		FileHelper.writeStringToFile(new File(outDir.getAbsolutePath()+"/service/"+serviceFileName), serviceSb.toString());
		FileHelper.writeStringToFile(new File(outDir.getAbsolutePath()+"/service/impl/"+serviceImplFileName), serviceImplSb.toString());
	}
	
	private static String getKeyTypeStr(String entityBeanContent){
		String keyType = null;
		String keyPatternStr = "([\\s\\S]*?) ([a-zA-Z]+) ([a-zA-Z]+);";
		Pattern singleKeyPattern = Pattern.compile("@Id"+keyPatternStr);
		Matcher singleKeyMather = singleKeyPattern.matcher(entityBeanContent);
		if(singleKeyMather.find()){
			keyType = singleKeyMather.group(2);
		}
		else{
			Pattern multiKeyPattern = Pattern.compile("@EmbeddedId"+keyPatternStr);
			Matcher multiKeyMather = multiKeyPattern.matcher(entityBeanContent);
			if(multiKeyMather.find()){
				keyType = multiKeyMather.group(2);
			}
			
		}
		System.out.println("keyType="+keyType);
		return keyType;
	}
	
	private static String getLengthStr(String dataType, String originLengthStr){
		if("DATE".equals(dataType) || "INTEGER".equals(dataType) || "SMALLINT".equals(dataType) || "".equals(originLengthStr.trim())){
			if("VARCHAR".equals(dataType)){
				return " (" + 50 + ")";
			}
			return "";
		}
		else if("DECIMAL".equals(dataType)){
			return "(11,2)";
		}
		else if(originLengthStr.endsWith(".0")){
			return " (" + originLengthStr.substring(0, originLengthStr.length()-2) + ")"; 
		}
		else{
			return " (" + originLengthStr + ")";
		}
		
	}
	
	private static String getIsNullStr( String isNull){
		if("N".equalsIgnoreCase(isNull)){
			return " NOT NULL";
		}
		return "";
	}
	
	private static String getKeyStr(String tableName, List<String> keyList){
		if(keyList == null || keyList.size() == 0){
			return "";
		}
		String keyStr = "";
		for(String key: keyList){
			keyStr += key + ",";
		}
		keyStr = keyStr.substring(0,keyStr.length()-1);
		return "	CONSTRAINT " + tableName + "_PK PRIMARY KEY (" + keyStr + ")\r\n";
	}
	
	public static void main(String[] args){
		try{
			xls2DDL("D:/01 work/program/Assess Contrl(Dummy)/Assess Control Table(Dummy).xlsx");
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}
